package com.lovestudying.dao.Impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import java.util.Vector;

import com.lovestudying.dao.AccountDao;
import com.lovestudying.entity.Account;
import com.lovestudying.util.DBUtil;
import com.lovestudying.util.DBUtil_Pro;
import com.lovestudying.util.PageInfo;
import com.sun.org.apache.regexp.internal.recompile;

/**
 * Account Dao 实现类
 * 
 * @author OldDeer
 *
 */
public class AccountDaoImpl implements AccountDao {

	@Override
	public Integer DeleteAccount(Map<String, Object> Where_Param) {
		// 创建sql语句
		StringBuffer sql = new StringBuffer("DELETE FROM Account");
		// Where参数列表
		Vector<Object> params = new Vector<>();
		// 根据Map中的情况构建 查询语句的条件
		if (Where_Param != null && Where_Param.size() != 0) {
			sql.append(" WHERE");
			int i = 0;
			for (Entry<String, Object> element : Where_Param.entrySet()) {
				sql.append(" " + element.getKey() + " = ?");
				params.add(element.getValue());
				i++;
				if (i < Where_Param.size()) {
					sql.append(" and");
				}
			}
		}

		return DBUtil_Pro.doUpdate(sql.toString(), params);
	}

	@Override
	public Integer InsertAccount(Map<String, Object> Value_Param) {
		// 创建sql语句
		StringBuffer sql = new StringBuffer("INSERT INTO  Account (");
		// values参数列表
		Vector<Object> params = new Vector<>();
		int i = 0;
		// 根据Map中的情况构建 Insert语句的条件
		for (Entry<String, Object> element : Value_Param.entrySet()) {
			sql.append(element.getKey());
			params.add(element.getValue());
			i++;
			if (i < Value_Param.size()) {
				sql.append(",");
			}
		}
		sql.append(") VALUES (");
		for (int j = 0; j < params.size(); j++) {
			sql.append("?");
			if (j < params.size() - 1) {
				sql.append(",");
			}
		}
		sql.append(")");
		return DBUtil_Pro.doUpdate(sql.toString(), params);
	}

	@Override
	public Integer UpdateAccount(Map<String, Object> Set_Param, Map<String, Object> Where_Param) {
		// 创建sql语句
		StringBuffer sql = new StringBuffer("UPDATE Account SET");
		// 参数列表
		Vector<Object> params = new Vector<>();

		int i = 0;
		// 根据Map中的情况构建 Insert语句的条件
		for (Entry<String, Object> element : Set_Param.entrySet()) {
			sql.append(" " + element.getKey() + " = ?");
			params.add(element.getValue());
			i++;
			if (i < Set_Param.size()) {
				sql.append(",");
			}
		}
		sql.append(" WHERE");
		int j = 0;
		for (Entry<String, Object> element : Where_Param.entrySet()) {
			sql.append(" " + element.getKey() + " = ?");
			params.add(element.getValue());
			j++;
			if (j < Where_Param.size()) {
				sql.append(" and");
			}
		}

		return DBUtil_Pro.doUpdate(sql.toString(), params);
	}

	@Override
	public PageInfo<Account> findAccount(Map<String, Object> Where_Param,
			Map<String, Object> OrdeBy_Param, Integer Lmt_pageSize, Integer Lmt_pageNum) {
		List<Account> list = new Vector<>();
		PageInfo<Account> AccountPage;
		ResultSet rs = null;
		// 创建sql语句
		StringBuffer sql = new StringBuffer("SELECT * FROM Account");
		// Where参数列表
		Vector<Object> params = new Vector<>();
		// 根据Map中的情况构建 查询语句的条件
		if (Where_Param != null && Where_Param.size() != 0) {
			sql.append(" WHERE");
			int i = 0;
			for (Entry<String, Object> element : Where_Param.entrySet()) {
				sql.append(" " + element.getKey() + " = ?");
				params.add(element.getValue());
				i++;
				if (i < Where_Param.size()) {
					sql.append(" and");
				}
			}
		}
		int total = 0;
		try {
			ResultSet last = DBUtil_Pro.doQuery(sql.toString(), params);

			last.last();
			total = last.getRow();
			// System.out.println(count);
		} catch (SQLException e1) {
			// TODO 自动生成的 catch 块
			e1.printStackTrace();
		}
		// 添加OrderBy参数
		if (OrdeBy_Param != null && OrdeBy_Param.size() != 0) {
			sql.append(" ORDER BY");
			int i = 0;
			// 遍历Map
			for (Entry<String, Object> element : OrdeBy_Param.entrySet()) {
				sql.append(" " + element.getKey());
				// 做升降序设置，键的值是null则是默认的ASC，不为null则添加DESC
				if (element.getValue() != null) {
					sql.append(" DESC");
				}
				i++;
				if (i < OrdeBy_Param.size()) {
					sql.append(",");
				}
			}
		}
		// 添加Limit参数
		if (Lmt_pageNum != null && Lmt_pageSize != null) {
			sql.append(" LIMIT ?,?");
			params.add((Lmt_pageNum - 1) * Lmt_pageSize);
			params.add(Lmt_pageSize);
		}

		// 执行SQL语句
		rs = DBUtil_Pro.doQuery(sql.toString(), params);
		try {
			while (rs.next()) {
				Account account = new Account(rs.getString("account_id"), rs.getString("account_phone"),
						rs.getString("account_name"), rs.getString("account_email"), rs.getString("account_psw"),
						rs.getString("account_pic"), rs.getInt("account_state"), rs.getInt("account_Integral"));
				list.add(account);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		AccountPage = new PageInfo<>(list, Lmt_pageNum, Lmt_pageSize, total);
		return AccountPage;
	}

	@Override
	public Account findAccountByPhone(String phone) {
		ResultSet rs = DBUtil.find(
				"select account_id,account_name,account_email,account_psw,account_pic,account_state,account_Integral from account where account_phone = ?",
				phone);

		Account account = new Account();
		try {
			while (rs.next()) {
				String accountId = rs.getString("account_id");
				String accountName = rs.getString("account_name");
				String accountEmail = rs.getString("account_email");
				String accountPsw = rs.getString("account_psw");
				String accountPic = rs.getString("account_pic");
				Integer accountState = rs.getInt("account_state");
				Integer accountIntegral = rs.getInt("account_Integral");

				account.setAccount_email(accountEmail);
				account.setAccount_id(accountId);
				account.setAccount_name(accountName);
				account.setAccount_pic(accountPic);
				account.setAccount_psw(accountPsw);
				account.setAccount_Integral(accountIntegral);
				account.setAccount_state(accountState);
			}

		} catch (SQLException e) {

			e.printStackTrace();
		}
		return account;
	}

	@Override
	public List<Account> findAccount() {

		List<Account> list = new ArrayList<>();

		ResultSet rs = DBUtil.doQuery(
				"select account_id,account_phone,account_name,account_email,account_state,account_Integral from account order by account_Integral desc");
		try {
			while (rs.next()) {
				String accountId = rs.getString("account_id");
				String accountName = rs.getString("account_name");
				String accountEmail = rs.getString("account_email");
				Integer accountState = rs.getInt("account_state");
				Integer accountIntegral = rs.getInt("account_Integral");
				String accountPhone = rs.getString("account_phone");
				Account account = new Account();
				account.setAccount_id(accountId);
				account.setAccount_name(accountName);
				account.setAccount_phone(accountPhone);
				account.setAccount_Integral(accountIntegral);
				account.setAccount_state(accountState);
				account.setAccount_email(accountEmail);
				list.add(account);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int deledeByAccountId(String accountId) {
		int delete = DBUtil.update("delete from account where account_id = ?", accountId);
		return delete;
	}

	@Override
	public int updateAccountStateByAccountId(String accountId, Integer accountState) {
		int rs = DBUtil.update("update account set account_state=? where account_id=?", accountState, accountId);

		return rs;
	}

	@Override
	public List<Account> findAccountByAccountName(String accountName) {
		List<Account> list = new ArrayList<>();

		ResultSet rs = DBUtil.doQuery(
				"select account_id,account_phone,account_name,account_email,account_state,account_Integral from account where account_name like ? order by account_Integral desc",
				"%" + accountName + "%");

		try {
			while (rs.next()) {
				String accountName1 = rs.getString("account_name");
				String accountId = rs.getString("account_id");
				String accountPhone = rs.getString("account_phone");
				String accountEmail = rs.getString("account_email");
				Integer accountState = rs.getInt("account_state");
				Integer accountIntegral = rs.getInt("account_Integral");
				Account account = new Account();
				account.setAccount_email(accountEmail);
				account.setAccount_id(accountId);
				account.setAccount_name(accountName1);
				account.setAccount_phone(accountPhone);
				account.setAccount_Integral(accountIntegral);
				account.setAccount_state(accountState);
				account.setAccount_name(accountName1);
				list.add(account);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}

	@Override
	public Integer updateAccountSubtractIntegral(String accountId, Integer coursePrice) {
		// TODO 自动生成的方法存根
		return DBUtil.update("UPDATE Account SET account_integral = account_integral - ? WHERE account_id = ?",
				coursePrice, accountId);
	}

}
